package com.zjdiepu.www.common.util;


import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.net.URL;
import java.net.URLConnection;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtil implements Serializable{

	/** 
	* @author zhangzj
	* @Fields serialVersionUID : 从excel导出
	* @version V1.0
	*/ 
	private static final long serialVersionUID = 1L;
	public static List<List<String>> getExcelContent(String file,int tdLength){  
        InputStream ins = null;
        Workbook wb = null;     
        try {
			ins=new FileInputStream(new File(file));
			wb= new XSSFWorkbook(ins);
			ins.close();     
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} 
        //3.得到Excel工作表对象  
        Sheet sheet = wb.getSheetAt(0);  
        //总行数  
        int trLength = sheet.getLastRowNum();  
        //4.得到Excel工作表的行  
//        Row row = sheet.getRow(0);  
        //总列数  
//        int tdLength = row.getLastCellNum();  
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> listStr= new ArrayList<String>();
        Object object=null;
        DecimalFormat df = null;
        for(int i=0;i<=trLength;i++){  
            //得到Excel工作表的行  
            Row row1 = sheet.getRow(i);  
            if(row1==null) continue;
            listStr= new ArrayList<String>();
            for(int j=0;j<tdLength;j++){  
            //得到Excel工作表指定行的单元格  
            Cell cel=(Cell)row1.getCell(0);
            if(cel==null || cel.getCellType() == Cell.CELL_TYPE_BLANK) continue;
            Cell cell1 = (Cell) row1.getCell(j);  
            /** 
             * 为了处理：Excel异常Cannot get a text value from a numeric cell 
             * 将所有列中的内容都设置成String类型格式 
             */  
            if(cell1==null){
            	listStr.add("");
            	continue;
            }
            switch(cell1.getCellType()){
            	case Cell.CELL_TYPE_STRING :{
            		object=cell1.getStringCellValue();
            	    break;
            	}
            	case Cell.CELL_TYPE_NUMERIC :
                {
            	 if (cell1.getCellStyle().getDataFormat() == 14) {
            		 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                     double value = cell1.getNumericCellValue();
                     Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                     object = sdf.format(date);
            	 }else{
            		 if(cell1.getNumericCellValue()==0){
            			 NumberFormat ddf1=NumberFormat.getNumberInstance() ;    
            			 ddf1.setMaximumFractionDigits(2);   
            			object=ddf1.format(cell1.getNumericCellValue());
            		 }else{
            			df = new DecimalFormat("#.00");
                      	object = df.format(cell1.getNumericCellValue());  
            		 }
                	
            	 }
                  break;
                }
                case Cell.CELL_TYPE_FORMULA :
                {
//                  cell1.setCellType(Cell.CELL_TYPE_NUMERIC);
//                  object=cell1.getNumericCellValue();
                	cell1.setCellType(Cell.CELL_TYPE_STRING);
                	object=cell1.getStringCellValue();
                	break;
                }
                case Cell.CELL_TYPE_BLANK :
                {
                  cell1.setCellType(Cell.CELL_TYPE_BLANK);
                  object=cell1.getStringCellValue();
                  break;
                }
            }
            if("".equals(cell1.toString().trim())){  
                cell1.setCellValue("");  
            }  
            listStr.add(object.toString());
            }
            if(listStr!=null && listStr.size()>0){
            	list.add(listStr);
            }
        }
        return list;
	}
	/** 
	* @author zhangzj
	* @Title: importExcel 
	* @Description: 批量导入到excel
	* @param @param excelPath
	* @param @param fileType
	* @param @param list    设定文件 
	* @return void    返回类型 
	* @version V1.0
	* @throws 
	*/
	public static void importExcel(String excelPath,String fileType,List<List<String>> list,List<String> listRow){
		Workbook wb = null; 
        File file = new File(excelPath);
        Sheet sheet =null;
        //创建工作文档对象   
        if (!file.exists()) {
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook();
                
            } else if(fileType.equals("xlsx")) {
                
                    wb = new XSSFWorkbook();
            } else {
                try {
					throw new Exception("文件格式不正确");
				} catch (Exception e) {
					e.printStackTrace();
				}
            }
            //创建sheet对象   
            sheet = (Sheet) wb.createSheet("sheet1");  
            OutputStream outputStream;
			try {
				outputStream = new FileOutputStream(excelPath);
				wb.write(outputStream);
				outputStream.flush();
				outputStream.close();
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
            
        } else {
            if (fileType.equals("xls")) {  
                wb = new HSSFWorkbook();  
                
            } else if(fileType.equals("xlsx")) { 
                wb = new XSSFWorkbook();  
                
            } else {  
                try {
					throw new Exception("文件格式不正确");
				} catch (Exception e) {
					e.printStackTrace();
				}
            }  
        }
         //创建sheet对象   
        if (sheet==null) {
            sheet = (Sheet) wb.createSheet("sheet1");  
        }
        
        //添加表头  
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        for(int i = 0;i < listRow.size();i++){  
            cell = row.createCell(i);  
            cell.setCellValue(listRow.get(i).toString());  
        }  
        //循环写入行数据   
        for (int i = 0; i < list.size(); i++) {  
            row = (Row) sheet.createRow(i+1);  
            row.setHeight((short) 500); 
            for(int j=0;j<=listRow.size();j++){
            	row.createCell(j).setCellValue(list.get(i).get(j));
            }
        }  
        
        //创建文件流   
        OutputStream stream;
		try {
			stream = new FileOutputStream(excelPath);
			//写入数据   
			wb.write(stream);  
			//关闭文件流   
			stream.close();  
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}  
	}
	/** 
	* 复制单个文件 
	* @param oldPath String 原文件路径 如：c:/fqf.txt 
	* @param newPath String 复制后路径 如：f:/fqf.txt 
	* @return boolean 
	*/ 
	public static void copyFile(String oldPath, String newPath) { 
		FileOutputStream fs=null;
		try { 
//			int bytesum =0; 
			int byteread = 0; 
			File oldfile = new File(oldPath); 
			if (oldfile.exists()) { //文件存在时 
			InputStream inStream = new FileInputStream(oldPath); //读入原文件 
			fs = new FileOutputStream(newPath); 
			byte[] buffer = new byte[1444]; 
			while ( (byteread = inStream.read(buffer)) != -1) { 
//			bytesum += byteread; //字节数 文件大小 
			fs.write(buffer, 0, byteread); 
			} 
			inStream.close(); 
		} 
		}catch (Exception e) { 
			System.out.println("复制单个文件操作出错"); 
			e.printStackTrace(); 
		} 

	} 
	 /**
	  * 文件复制
	  * @param src 源文件
	  * @param dst目标文件
	  */
	 public static void excelCopy(File src, File dst) {
	  try {
	   InputStream in = null;
	   OutputStream out = null;
	   try {
	    in = new BufferedInputStream(new FileInputStream(src),1024);
	    out = new BufferedOutputStream(new FileOutputStream(dst),1024);
	    byte[] buffer = new byte[1024];
	    int len; 
	    while ((len=in.read(buffer))!=-1) {
	     out.write(buffer,0,len);
	    }
	   } finally {
	    if (null != in) {
	     in.close();
	    }
	    if (null != out) {
	     out.close();
	    }
	   }
	  } catch (Exception e) {
	   e.printStackTrace();
	  }
	 }
	 
	 /**
	 *下载excel 
	 */
	 public static void downloadFile(URL theURL, String filePath) throws IOException {  
	     File dirFile = new File(filePath);
	        if(!dirFile.exists()){//文件路径不存在时，自动创建目录
	          dirFile.mkdir();
	        }
	      URLConnection  connection = theURL.openConnection();
	      InputStream in = connection.getInputStream();  
	      FileOutputStream os = new FileOutputStream(filePath); 
	      byte[] buffer = new byte[4 * 1024];  
	      int read;  
	      while ((read = in.read(buffer)) > 0) {  
	          os.write(buffer, 0, read);  
           }  
        os.close();  
        in.close();
	   }  
}
